import pandas as pd
import os

# file1和file2两个文件的路径
file1_path = './data/file1.xlsx'
files2_path = './data/base'
df1 = pd.read_excel(file1_path)
for file2_name in os.listdir(files2_path):
    file2_path = os.path.join(files2_path, file2_name)
    print(file2_name)
    df2 = pd.read_excel(file2_path)
    if 'company_name' not in df1.columns or 'company_name' not in df2.columns:
        raise ValueError("Both tables must have a 'company_name' column.")
    if 'Products_and_Services' not in df2.columns:
        raise ValueError("Table 2 must have a 'Product_Service' column.")
    merged_file = pd.merge(df1, df2[['company_name', 'Products_and_Services']], on='company_name', how='left')
    merged_file.to_excel('./data/merged_file11.xlsx', index=False)
